package com.zy.explore.license.infrastructure.plugins.util;

import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * 建类说明：mysql语句转达梦数据库语句工具类
 *
 * @author 中国印
 * @date 2022年02月17日 23:41:01
 */
public class MysqlToDmUtil {


    /**
     * <p>方法说明：将每一行mysql的sql语句转换成达梦的语句</p>
     *
     * @param line 一行字符串
     * @return {@link String }
     */
    public static String transformMysqlToDm(String line) {

        // ② oracle中不能使用“ ` ”符号
        line = StringUtils.replace(line, "`", "");

        // ③ CONCAT('%', #{name}, '%')  ->     '%'||#{name}||'%' （Oracle中concat函数只能放两个参数）
        if (line.contains("concat")) {
            // String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);
            line = line.replaceAll(",", " || ");
            line = line.replaceAll("concat", "");
        }
        if (line.contains("CONCAT")) {
            // String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);
            line = line.replaceAll(",", " || ");
            line = line.replaceAll("CONCAT", "");
        }

        // ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳
        line = line.replaceAll("UNIX_TIMESTAMP", "ORACLE_TO_UNIX");

        // ⑤ 部分关键字需加上双引号 TODO 【注： 字段名大写，映射的别名需保存原本小写！】 `level -> "LEVEL"`  `user -> "USER"`      `number -> "NUMBER"`  `desc -> "DESC"`
        List<String> keywordList = new ArrayList<>(Arrays.asList("level", "user", "number"));
        if (!line.contains("test=")) {
            for (String e : keywordList) {
                // StringUtils.swapCase(e) : 大小写互换
                line = line.replaceAll(" " + e + " ", " \"" + StringUtils.swapCase(e) + "\" ");
                line = line.replaceAll("." + e + " ", "\\.\"" + StringUtils.swapCase(e) + "\" ");
                if (line.endsWith(e) || line.endsWith(e + ",")) {
                    line = line.replaceAll(e, "\"" + StringUtils.swapCase(e) + "\"");
                }
            }
        }
        if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {
            line = line.replaceAll(" date", " \"date\"");
            line = line.replaceAll(" date,", " \"date,\"");
            line = line.replaceAll(" 'date'", " \"date\"");
            line = line.replaceAll(" 'DATE'", " \"date\"");
            line = line.replaceAll(" DATE", " \"date\"");
        }
        line = line.replaceAll(" date ", " \"date\" ");
        line = line.replaceAll(" DATE ", " \"date\" ");

        // ⑥ `IFNULL` -> `NVL`
        line = line.replaceAll("IFNULL", "NVL");
        line = line.replaceAll("ifnull", "NVL");

        // ⑦ 时间 `str_to_date` -> `to_date`     `date_format` -> `to_char`
        // `%Y-%m-%d`  -> `yyyy-MM-dd`    `%Y-%m` -> `yyyy-MM`
        line = line.replaceAll("str_to_date", "TO_DATE");
        line = line.replaceAll("STR_TO_DATE", "TO_DATE");
        line = line.replaceAll("date_format", "TO_CHAR");
        line = line.replaceAll("DATE_FORMAT", "TO_CHAR");

        // 这里注意替换顺序问题，最长的应该放最前面！！！
        line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");
        line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");
        line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");
        line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");
        line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");
        line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");
        line = line.replaceAll("%Y-%m", "yyyy-MM");
        line = line.replaceAll("%Y", "yyyy");
        line = line.replaceAll("%H", "HH24");
        line = line.replaceAll("%k", "HH24");

        line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
        line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");

        // ⑧ ...

        // 需手动处理的SQL 【 group by | 批量插入 | ... 】
        return line;
    }

    public static void main(String[] args) {

        String sql = "DROP TABLE IF EXISTS `security_org_department`;\n" +
                "CREATE TABLE `security_org_department` (\n" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',\n" +
                "  `create_user` int(11) DEFAULT NULL COMMENT '创建人',\n" +
                "  `create_time` datetime DEFAULT NULL COMMENT '创建时间',\n" +
                "  `update_user` int(11) DEFAULT NULL COMMENT '修改人',\n" +
                "  `update_time` datetime DEFAULT NULL COMMENT '修改时间',\n" +
                "  `delete_user` int(11) DEFAULT NULL COMMENT '删除人',\n" +
                "  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',\n" +
                "  `data_version` int(11) DEFAULT NULL COMMENT '数据版本',\n" +
                "  `data_state` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据的逻辑状态（1：有效，非1：无效）',\n" +
                "  `tenant_id` int(11) DEFAULT NULL COMMENT '租户id',\n" +
                "  `name` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门名称',\n" +
                "  `pid` int(11) DEFAULT NULL COMMENT '父id',\n" +
                "  `dept_type` int(11) DEFAULT NULL COMMENT '部门类型，1：实体组织，2：区划，3：虚拟组织，4：部门/团队',\n" +
                "  `dept_code` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '编码',\n" +
                "  `industry_category` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '行业分类',\n" +
                "  `credit_code` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '统一社会信用代码',\n" +
                "  `dept_level` int(11) DEFAULT NULL COMMENT '级别（根节点算做1级）',\n" +
                "  `custom_order` int(11) DEFAULT NULL COMMENT '自定义顺序',\n" +
                "  `remark` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',\n" +
                "  `id_path` varchar(190) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'id的路径，用于树结构检索，不包含自己，举例：/1/2/3/',\n" +
                "  `name_path` varchar(190) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称的路径，包含自己，结尾不包含分隔符',\n" +
                "  `inside_id` int(11) DEFAULT NULL COMMENT '内部id，用于区分不同租户下的同一部门',\n" +
                "  `children_num` int(11) DEFAULT NULL COMMENT '当前部门的子部门数量',\n" +
                "  `current_user_num` int(11) DEFAULT NULL COMMENT '当前部门的用户数',\n" +
                "  `recursion_user_num` int(11) DEFAULT NULL COMMENT '递归所有子部门的用户数，包含当前部门',\n" +
                "  `recursion_distinct_user_num` int(11) DEFAULT NULL COMMENT '递归所有子部门去重后的用户数，包含当前部门',\n" +
                "  `level1` int(11) DEFAULT NULL COMMENT '一级部门的id',\n" +
                "  `level2` int(11) DEFAULT NULL COMMENT '二级部门的id',\n" +
                "  `level3` int(11) DEFAULT NULL COMMENT '三级部门的id',\n" +
                "  `level4` int(11) DEFAULT NULL COMMENT '四级部门的id',\n" +
                "  `level5` int(11) DEFAULT NULL COMMENT '五级部门的id',\n" +
                "  `level6` int(11) DEFAULT NULL COMMENT '六级部门的id',\n" +
                "  `level7` int(11) DEFAULT NULL COMMENT '七级部门的id',\n" +
                "  `level8` int(11) DEFAULT NULL COMMENT '八级部门的id',\n" +
                "  `level9` int(11) DEFAULT NULL COMMENT '九级部门的id',\n" +
                "  `level10` int(11) DEFAULT NULL COMMENT '十级部门的id',\n" +
                "  PRIMARY KEY (`id`),\n" +
                "  UNIQUE KEY `security_org_department_1` (`pid`,`name`,`data_state`,`tenant_id`),\n" +
                "  KEY `security_org_department_2` (`id_path`),\n" +
                "  KEY `security_org_department_3` (`level1`),\n" +
                "  KEY `security_org_department_4` (`level2`),\n" +
                "  KEY `security_org_department_5` (`level3`),\n" +
                "  KEY `security_org_department_6` (`level4`),\n" +
                "  KEY `security_org_department_7` (`level5`),\n" +
                "  KEY `security_org_department_8` (`level6`),\n" +
                "  KEY `security_org_department_9` (`level7`),\n" +
                "  KEY `security_org_department_10` (`level8`),\n" +
                "  KEY `security_org_department_11` (`level9`),\n" +
                "  KEY `security_org_department_12` (`level10`)\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=10017 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='组织结构的部门';\n" +
                "\n";

        System.out.println("原DDL:\n" + sql);
        System.out.println("================================================");
        String newSql = transformDdlFromMysqlToOracleOrDm(sql);
        System.out.println("转化之后的DDL:\n" + newSql);

        // try {
        //     mysqlDdlToPostgresDdl("/Users/zhuyin/test_ddl.sql");
        // } catch (IOException | JSQLParserException e) {
        //     e.printStackTrace();
        // }
    }

    /**
     * 方法说明：将mysql的DDL语句转为oracle或dm的DDL语句
     *
     * @param ddl sql
     * @return {@link String }
     */
    private static String transformDdlFromMysqlToOracleOrDm(String ddl) {
        String[] ddls = ddl.split("\n");
        boolean isStart = false;
        boolean isEnd = false;
        StringBuilder newSql = new StringBuilder();
        String tableName = null;
        String colName = null;
        String comment = "";
        int length;
        for (String s : ddls) {
            if (s.startsWith("DROP TABLE ")) {
                newSql = new StringBuilder();
                comment = "";
                isEnd = false;
                isStart = false;
                // System.out.println(s);
            } else if (s.startsWith("CREATE TABLE ")) {
                isStart = true;
                length = s.split("CREATE TABLE ")[1].length();
                tableName = s.split("CREATE TABLE ")[1].substring(0, length - 2).replaceAll("`", "");
                //  System.out.println(tableName);
                newSql = new StringBuilder(s.replaceAll("`", ""));
            } else {
                if (isStart) {
                    if (s.startsWith("  PRIMARY KEY ") || s.startsWith("  UNIQUE KEY ") || s.startsWith("  KEY ")) {
                        continue;
                    }
                    s = s.replaceAll("int\\(11\\)", "decimal(11)").replaceAll("bigint\\(20\\)", "decimal(11)").replaceAll("tinyint\\(4\\)", "decimal(11)").replaceAll("tinyint\\(1\\)", "decimal(11)").replaceAll("timestamp\\(3\\)", "timestamp").replaceAll("datetime", "timestamp").replaceAll("`", "").replaceAll("NOT NULL", "").replaceAll("DEFAULT NULL", "");

                    if (s.indexOf("AUTO_INCREMENT") > 0) {
                        s = s.replaceAll(" AUTO_INCREMENT .*,", "PRIMARY KEY,");
                    }
                    if (s.indexOf("COMMENT") > 0 && s.indexOf("ENGINE=InnoDB") < 1) {
                        length = s.split("COMMENT")[1].length();
                        comment = comment + "COMMENT on column \"" + tableName + "\".\"" + s.split("\\s+")[1] + "\" is" + s.split("COMMENT")[1].substring(0, length - 1) + ";" + "\n";

                        s = s.split("COMMENT")[0] + ",";
                    }

                    if (s.indexOf("ENGINE=InnoDB") > 0) {
                        if (s.indexOf("COMMENT") > 0) {
                            length = s.split("COMMENT")[1].length();
                            comment = comment + "COMMENT on table \"" + tableName + "\" is " + s.split("COMMENT=")[1].substring(0, length - 1);
                        }
                        isEnd = true;
                        length = newSql.length();
                        newSql = new StringBuilder(newSql.substring(0, length - 2));
                        newSql.append(");");
                    } else {
                        newSql.append(s).append("\n");
                    }
                }
            }
        }
        // 必须转成大写
        return (newSql + "\n" + comment).toUpperCase();
    }


    // /**
    //  * 方法说明：mysql DDL 转 postgres DDL
    //  *
    //  * @param mysqlDdlPath mysql DDL文件路径
    //  * @throws IOException         IO异常
    //  * @throws JSQLParserException 解析异常
    //  */
    // public static void mysqlDdlToPostgresDdl(String mysqlDdlPath) throws IOException, JSQLParserException {
    //     // 你的MySQL DDL路径
    //     // String mysqlDdlPath = "/Users/reno/Downloads/mysql.sql";
    //     String ddls = FileUtils.readFileToString(new File(mysqlDdlPath), StandardCharsets.UTF_8);
    //     System.out.println(ddls);
    //     System.out.println("++++++++++开始转换SQL语句+++++++++++++");
    //     Statements statements = CCJSqlParserUtil.parseStatements(ddls);
    //     statements.getStatements().stream().map(statement -> (CreateTable) statement).forEach(ct -> {
    //         Table table = ct.getTable();
    //         List<ColumnDefinition> columnDefinitions = ct.getColumnDefinitions();
    //         List<String> comments = new ArrayList<>();
    //         List<ColumnDefinition> collect = columnDefinitions.stream().peek(columnDefinition -> {
    //             List<String> columnSpecStrings = columnDefinition.getColumnSpecStrings();
    //             int commentIndex = getCommentIndex(columnSpecStrings);
    //             if (commentIndex != -1) {
    //                 int commentStringIndex = commentIndex + 1;
    //                 String commentString = columnSpecStrings.get(commentStringIndex).toString();
    //                 String commentSql = genCommentSql(table.toString(), columnDefinition.getColumnName(), commentString);
    //                 comments.add(commentSql);
    //                 columnSpecStrings.remove(commentStringIndex);
    //                 columnSpecStrings.remove(commentIndex);
    //             }
    //             columnDefinition.setColumnSpecStrings(columnSpecStrings);
    //         }).collect(Collectors.toList());
    //         ct.setColumnDefinitions(collect);
    //         String createSql = ct.toString().replaceAll("`", "\"")
    //                 .replaceAll("BIGINT UNIQUE NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
    //                 .replaceAll("BIGINT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
    //                 .replaceAll("BIGINT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
    //                 .replaceAll("INT NOT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
    //                 .replaceAll("INT NULL AUTO_INCREMENT", "BIGSERIAL PRIMARY KEY")
    //                 .replaceAll("IF NOT EXISTS", "")
    //                 .replaceAll("TINYINT", "SMALLINT")
    //                 .replaceAll("DATETIME", "TIMESTAMP")
    //                 .replaceAll(", PRIMARY KEY ", "");
    //         // 如果存在表注释
    //         if (createSql.contains("COMMENT")) {
    //             createSql = createSql.substring(0, createSql.indexOf("COMMENT"));
    //         }
    //         System.out.println(createSql + ";");
    //         comments.forEach(t -> System.out.println(t.replaceAll("`", "\"") + ";"));
    //     });
    // }
    //
    // /**
    //  * 获得注释的下标
    //  */
    // private static int getCommentIndex(List<String> columnSpecStrings) {
    //     for (int i = 0; i < columnSpecStrings.size(); i++) {
    //         if ("COMMENT".equalsIgnoreCase(columnSpecStrings.get(i))) {
    //             return i;
    //         }
    //     }
    //     return -1;
    // }
    //
    // /**
    //  * 生成COMMENT语句
    //  *
    //  * @param table        表名
    //  * @param column       字段名
    //  * @param commentValue 描述文字
    //  * @return COMMENT语句
    //  */
    // private static String genCommentSql(String table, String column, String commentValue) {
    //     return String.format("COMMENT ON COLUMN %s.%s IS %s", table, column, commentValue);
    // }

}
